import os,json
if os.path.exists("./test.json"):
    with open("test.json","r") as fp:
        json.str1 = fp.read()
        dit = json.loads(json.str1)
        print(dit)
else:
    dit = {"苹果": {"价格": 20, "库存": 200}, "西瓜": {"价格": 50, "库存": 300}}
    json.str1 = json.dumps(dit)
    with open("test.json",'w') as fp:
        fp.write(json.str1)
while 1:
    print("""----------------------------
        欢迎来到商品购物系统：
        添加商品请按1
        查询商品请按2
        删除商品请按3
        修改商品请按4
        退出系统请按5
----------------------------
    """)
    n = int(input("请输入你想进行的功能："))
    if n == 1:
        name = input("请输入你要添加的商品名：")
        if name in dit:
            print("您添加的商品已经存在")
        else:
            price = int(input("请输入商品价格："))
            num = int(input("请输入商品库存："))
            dit[name]={"价格": price, "库存": num}
            print(f"已将{name}添加完成")
    elif n == 2:
        name = input("请输入您要查询的商品名：")
        if name in dit:
            print(f"{name}：价格{dit[name]['价格']},库存{dit[name]['库存']}")
        else:
            print("您查询的商品不存在")
    elif n == 3:
        name = input("请输入您要删除的商品名：")
        if name in dit:
            dit.pop(name)
            print(f"已删除{name}")
        else:
            print("您要删除的商品不存在")
    elif n == 4:
        name = input("请输入您要修改的商品名：")
        if name in dit:
            price = int(input("请输入您要修改的商品价格："))
            num = int(input("请输入您要修改的商品库存："))
            dit[name]={"价格":price,"库存":num}
            print("已完成修改")
        else:
            print("您要修改的商品不存在")
    elif n == 5:
        print("正在退出商品购物系统......")
        json.str2 = json.dumps(dit)
        with open("test.json", 'w') as fp:
            fp.write(json.str2)
        print("退出成功！！！！")
        break

from sqlalchemy import create_engine,text
host="192.168.20.133"
user="root"
password="123456"
port=3306
db="test2"
DB_UPI = f'mysql+pymysql://{user}:{password}@{host}:{port}/{db}?charset=utf8'
engin = create_engine(DB_UPI)
conn = engin.connect()

sql="drop table if exists app3 "
conn.execute(text(sql))
sql="""
    create table app3(
        name varchar(15),
        price int ,
        num int
    )
"""
conn.execute(text(sql))
for fruit, details in dit.items():
    fruit_data = {
        "name": fruit,
        "price": details["价格"],
        "num":details["库存"]
    }
    insert_sql = "INSERT INTO app3 (name,price,num) VALUES (:name, :price, :num)"
    conn.execute(text(insert_sql), fruit_data)
conn.commit()
